/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tesam.gedeon.dao.impl;

import com.tesam.gedeon.dao.SimcardDao;
import com.tesam.gedeon.entity.Network;
import com.tesam.gedeon.entity.Provider;
import com.tesam.gedeon.entity.Simcard;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Sistema
 */
@Repository
public class SimcardDaoImpl extends SimpleJdbcDaoSupport implements SimcardDao  {

    @Autowired
    public SimcardDaoImpl(DataSource dataSource) {
        setDataSource(dataSource);
    }
    
    @Override
    public int getCountAll() {
        return getJdbcTemplate().queryForInt("select count(*) as total from simcard si " +
                                              "inner join  network ne  on ne.id = si.networkid " +
                                              "inner join  provider pr on pr.id = si.providerid " +
                                              "where si.deleted = 0");
    }

    @Override
    public int getCountBySearch(Simcard simcard) {
        return getJdbcTemplate().queryForInt("select count(*) as total from simcard si " +
                                              "inner join  network ne  on ne.id = si.networkid " +
                                              "inner join  provider pr on pr.id = si.providerid " +
                                              "where si.deleted = 0 and upper(si.iccid) like '%"+simcard.getIccid().toUpperCase()+"%'");
    }

    @Override
    public List<Simcard> getSimcardByLimit(int startLimit, int endLimit, String dataToSort, String directionOfOrder) {
        List<Simcard> result = new ArrayList();
        try{
            String sql = "select si.* , ne.description as networkName , pr.name  as providerName from simcard si " +
                                              "inner join  network ne  on ne.id = si.networkid " +
                                              "inner join  provider pr on pr.id = si.providerid " +
                                              "where si.deleted = 0 " + 
                                              "order by "+dataToSort+" "+directionOfOrder+" limit "+
                                              endLimit+" offset "+startLimit;
            
             List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                for (Map row : rows) {
		Simcard simcard = new Simcard();
		simcard.setId((Integer)(row.get("id"))); 
                simcard.setIccid((String)(row.get("iccid")));
                simcard.setImsi((String)(row.get("imsi")));
                simcard.setMovilName((String)(row.get("movilename")));
                simcard.setMsisdn((String)(row.get("misdn")));
                simcard.setPlantype((String)(row.get("plantype")));
                simcard.setType((String)(row.get("type")));
                
                
                Network network = new Network();
                network.setDescription((String)row.get("networkName"));
                network.setId((Integer)row.get("networkid")); 
                simcard.setNetwork(network);
                
                Provider provider = new Provider();
                provider.setId((Integer)row.get("providerid"));
                provider.setName((String)row.get("providerName")); 
                simcard.setProvider(provider);
                
		result.add(simcard);
                }
        }catch(Exception e){
            System.out.println("getSimcardByLimit : "+e.getMessage());
        }
        return result ;
    }

    @Override
    public List<Simcard> getSimcardBySearch(int startLimit, int endLimit, String dataToSort, String directionOfOrder,Simcard simcard) {
        List<Simcard> result = new ArrayList();
        try{
            String sql = "select si.* , ne.description as networkName , pr.name  as providerName from simcard si " +
                         "inner join  network ne  on ne.id = si.networkid " +
                         "inner join  provider pr on pr.id = si.providerid " +
                         "where si.deleted = 0  and upper(si.iccid) like '%"+simcard.getIccid().toUpperCase()+"%' " + 
//                         " or upper(si.imsi) like '%"+valSearch+"%'"+
//                         " or upper(ne.description) like '%"+valSearch+"%'"+
                         "order by "+dataToSort+" "+directionOfOrder+" limit "+
                                              endLimit+" offset "+startLimit;
              System.out.println(sql);
             List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                for (Map row : rows) {
		Simcard simcardE = new Simcard();
		simcardE.setId((Integer)(row.get("id"))); 
                simcardE.setIccid((String)(row.get("iccid")));
                simcardE.setImsi((String)(row.get("imsi")));
                simcardE.setMovilName((String)(row.get("movilename")));
                simcardE.setMsisdn((String)(row.get("misdn")));
                simcardE.setPlantype((String)(row.get("plantype")));
                simcardE.setType((String)(row.get("type")));
                
                
                Network network = new Network();
                network.setDescription((String)row.get("networkName"));
                network.setId((Integer)row.get("networkid")); 
                simcardE.setNetwork(network);
                
                Provider provider = new Provider();
                provider.setId((Integer)row.get("providerid"));
                provider.setName((String)row.get("providerName")); 
                simcardE.setProvider(provider);
                
		result.add(simcardE);
                }
        }catch(Exception e){
            System.out.println("getSimcardByLimit : "+e.getMessage());
        }
        return result ;
    }

    @Override
    public List<Simcard> getSimcardById(Simcard simcard) {
        List<Simcard> result = new ArrayList();
        try{
            getJdbcTemplate().update("update simcard set viewedtime = NOW() where id = "+simcard.getId());
            
            String sql = "select si.* , ne.description as networkName , pr.name  as providerName from simcard si " +
                         "inner join  network ne  on ne.id = si.networkid " +
                        "inner join  provider pr on pr.id = si.providerid " +
                       "where si.deleted = 0 "
                        + " and si.id = ?" ;
              
             List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql , simcard.getId());
                for (Map row : rows) {
		Simcard simcardE = new Simcard();
		simcardE.setId((Integer)(row.get("id"))); 
                simcardE.setIccid((String)(row.get("iccid")));
                simcardE.setImsi((String)(row.get("imsi")));
                simcardE.setMovilName((String)(row.get("movilename")));
                simcardE.setMsisdn((String)(row.get("misdn")));
                simcardE.setPlantype((String)(row.get("plantype")));
                simcardE.setType((String)(row.get("type")));
                
                
                Network network = new Network();
                network.setDescription((String)row.get("networkName"));
                network.setId((Integer)row.get("networkid")); 
                simcardE.setNetwork(network);
                
                Provider provider = new Provider();
                provider.setId((Integer)row.get("providerid"));
                provider.setName((String)row.get("providerName")); 
                simcardE.setProvider(provider);
                
		result.add(simcardE);
                }
        }catch(Exception e){
            System.out.println("getSimcardByLimit : "+e.getMessage());
        }
        return result ;
    }

    @Override
    public void delete(Simcard simcard) {
        getJdbcTemplate().update("update simcard set deleted = 1 where id = "+simcard.getId());
    }

    @Override
    public void update(Simcard simcard) {
        getJdbcTemplate().update(" update simcard set datemodified = NOW() ,modifiedby = ? , iccid = ? , providerid = ? , "
                + "networkid = ? , imsi = ? , plantype = ? where id = ?" , simcard.getModifiedBy() , simcard.getIccid() ,
                simcard.getProvider().getId() , simcard.getNetwork().getId() , simcard.getImsi() , simcard.getPlantype() ,
                simcard.getId());
        
    }

    @Override
    public int save(Simcard simcard) {
        int id = 0;
        try{ 
            getJdbcTemplate().update("insert into simcard (createdby , datecreated , status , deleted , "
                                 +   "presence , ownerid , iccid , providerid , networkid , imsi , "
                                   + "msisdn ,type , movilename , plantype) values ( ? , NOW() ,"
                    + "0 , 0 , 1 , ? , ? , ? , ? , ? , "
                    + "? , ? , ? , ?)" , simcard.getCreatedBy(),simcard.getOwnerId() , simcard.getIccid() ,
                    simcard.getProvider().getId() , simcard.getNetwork().getId() , simcard.getImsi() , 
                    simcard.getMsisdn() , simcard.getType() , simcard.getMovilName() , simcard.getPlantype());
            
            id = getSimpleJdbcTemplate().queryForInt("SELECT last_value FROM entity_id_seq");
            
        }catch(Exception e){
            System.out.println("save simcard : "+e.getMessage());
        }
        return id ;
    }
    
    @Override
    public int getCountByIccid(Simcard simcard){
         
        return getJdbcTemplate().queryForInt("select count(*) as total from simcard where iccid = '"+simcard.getIccid()+"'");
    }
    
}
